package com.scalar.forecast.salesForecastEntry.actionCommands;

import com.scalar.forecast.actionCommands.ActionCommand;
import com.scalar.forecast.salesForecastEntry.forms.SalesForecastEntryForm;
import com.scalar.forecast.salesForecastEntry.ForecastSheetData;
import com.scalar.forecast.salesForecastEntry.dao.SalesForecastEntryDAO;
import com.scalar.forecast.commons.SessionKeys;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.FileUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.ScalarException;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForm;
import org.apache.struts.upload.FormFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.util.*;
import java.io.InputStream;
import java.io.File;

import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;

/**
 * User: Sujan
 * Date: Jul 30, 2007
 * Time: 1:08:25 PM
 */
public class SalesForecastUploadCommand extends ActionCommand {
    public ActionForward execute(ActionMapping mapping,
                                 ActionForm form,
                                 HttpServletRequest request,
                                 HttpServletResponse response) throws Exception
    {
        SalesForecastEntryForm forecastEntryForm =  ( SalesForecastEntryForm ) form;
        HttpSession session = request.getSession();
        User user = (User)session.getAttribute( SessionKeys.USER_KEY );
        LinkedHashMap errors = new LinkedHashMap();
        HashMap info = new HashMap();
        request.setAttribute( SessionKeys.INFO_MESSAGES, info );
        request.setAttribute( SessionKeys.ERROR_MESSAGES, errors );

        FormFile salesForecastFile = forecastEntryForm.getSalesForecastFile();
        ArrayList<ForecastSheetData> forecastDataList = null;
        File tmpfile = null;
        try
        {
            tmpfile = File.createTempFile("tmp",".xls");
            tmpfile.deleteOnExit();
            FileUtils.saveFile(tmpfile.getName(), tmpfile.getParent(), salesForecastFile.getInputStream());
            forecastDataList = parseSalesForecastFile(tmpfile);
            //forecastDataList = parseSalesForecastFile( salesForecastFile.getInputStream() );
        }
        catch ( Exception e )
        {
            errors.put( "Could not parse Forecast Data", null );
            e.printStackTrace();
            return mapping.findForward( "salesForecastUploadView" );
        } finally {
            if(tmpfile != null && tmpfile.exists()) {
                tmpfile.delete();
            }
        }

        DBTransaction dbTransaction = null;
        boolean success = false;
        try
        {
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            SalesForecastEntryDAO.updateForecastSheetData ( dbTransaction, forecastDataList, user.getUserId() );
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if ( dbTransaction != null )
            {
                if ( success )
                {
                    dbTransaction.commit();
                }
                else
                {
                    dbTransaction.rollback();
                }
            }
        }
        if ( success )
        {
            try
            {
                String msg = getAlertMailMessage ( forecastDataList );
                sendProductPortingAlert( "Forecast porting status alert", msg, ConfigManager.getFromEmailId( null ), user.getEmail() );
                info.put ( "Porting Status Email ", " has been sent Successfully" );
            }
            catch ( ScalarException e )
            {
                errors.put( "Could not send porting status alert mail", null );
            }
        }
        else
        {
            errors.put( "Could not update Forecast Data", null );
        }
        return mapping.findForward( "salesForecastUploadView" );
    }

    private ArrayList parseSalesForecastFile ( InputStream fileStream ) throws Exception
    {
        Workbook workbook 	        = Workbook.getWorkbook( fileStream );
        Sheet sheet 		        = workbook.getSheet( 0 );
        Cell[] cells 			    = null;
        int noOfCells               = 0;
        int noOfRows                = sheet.getRows();
        ArrayList forecastDataList = new ArrayList();
        for ( int rowCount = 1; rowCount < noOfRows; rowCount++ )
        {
            cells = sheet.getRow( rowCount );
            noOfCells = cells.length;
            ForecastSheetData forecastData = new ForecastSheetData();
            if ( noOfCells >= 7 )
            {
                if ( cells[0].getContents() != null && cells[0].getContents().trim().length() > 0 ) //SalesPerson Name available
                {
                    forecastData.setSalesPerson( cells[0].getContents().trim() );
                }
                if ( cells[1].getContents() != null && cells[1].getContents().trim().length() > 0 ) //Customer Name available
                {
                    forecastData.setCustomerName( cells[1].getContents().trim() );
                }
                if ( cells[2].getContents() != null && cells[2].getContents().trim().length() > 0 )
                {
                    forecastData.setProjName( forecastData.getSalesPerson() + "-" + forecastData.getCustomerName() + "-" + cells[2].getContents().trim() );
                }
                if ( cells[3].getContents() != null && cells[3].getContents().trim().length() > 0 )
                {
                    forecastData.setPartNumber( cells[3].getContents().trim() );
                }
                if ( cells[4].getContents() != null && cells[4].getContents().trim().length() > 0 )
                {
                    forecastData.setYear( Integer.parseInt( cells[4].getContents().trim() ) );
                }
                if ( cells[6].getContents() != null && cells[6].getContents().trim().length() > 0 )
                {
                    double qty = 0;
                    try
                    {
                        qty = Double.parseDouble( cells[6].getContents().trim() );
                    }
                    catch ( NumberFormatException e ) {}

                    if ( qty > 0 )
                    {
                        forecastData.setQty( (int)(qty * 1000) );
                    }
                    else
                    {
                        continue;
                    }
                }
                if ( noOfCells > 7 )
                {
                    if ( cells[7].getContents() != null && cells[7].getContents().trim().length() > 0 )
                    {
                        forecastData.setComments( cells[7].getContents().trim() );
                    }
                }
                if ( cells[5].getContents() != null && cells[5].getContents().trim().length() > 0 )
                {
                    String monthStr = cells[5].getContents().trim();
                    int idx = monthStr.indexOf( "~" );
                    if ( idx > 0 ) //month range
                    {
                        int fromMonth = Integer.parseInt ( monthStr.substring( 4, idx ) );
                        int toMonth = Integer.parseInt( monthStr.substring( (idx+1) ) );
                        if ( fromMonth <= toMonth )
                        {
                            for ( int i = fromMonth; i <= toMonth; i++ )
                            {
                                ForecastSheetData forecastData1 = (ForecastSheetData)forecastData.clone();
                                forecastData1.setMonth( i-1 ); //Month starts from Zero
                                forecastData1.setQty( forecastData1.getQty()/((toMonth-fromMonth)+1) );
                                if ( i == fromMonth )
                                {
                                    forecastData1.setQty( forecastData1.getQty() + ( forecastData.getQty()%((toMonth-fromMonth)+1) ) );
                                }
                                forecastDataList.add( forecastData1 );
                            }
                        }
                    }
                    else
                    {
                        forecastData.setMonth( Integer.parseInt ( monthStr.substring( 4 )  ) - 1 );
                        forecastDataList.add ( forecastData );
                    }
                }
            }
            /*else
            {
                //log ( "Could not found Customer Number Cell for the Row:" + (rowCount+1) );
                System.out.println ( "Could not parse Forecast Data for the Row" + ( rowCount+1) + ":noOfCells:" + noOfCells );
            }*/
        }
        return forecastDataList;
    }
    private void sendProductPortingAlert ( String subject, String message, String fromEmail, String toEmails ) throws ScalarException
    {
        try
        {
            Mailer.send ( fromEmail, toEmails, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not send email notifications", e );
        }
    }

    private String getAlertMailMessage( ArrayList forecastDataList )
    {
        StringBuffer alertMailMsg = new StringBuffer();
        StringBuffer infoMsg = new StringBuffer();
        ForecastSheetData forecastData = null;
        final int PART_NUMBER_NOT_EXIST = 1;
        final int PROJECT_NOT_EXIST = 2;
        final int PROJECT_PRODUCT_NOT_EXIST = 3;
        alertMailMsg.append ( "<HTML><HEAD><TITLE>Forecast Data Porting Status</TITLE></HEAD>" );
        alertMailMsg.append ( "<BODY><TABLE STYLE='FONT-SIZE: 8PT; COLOR: BLACK; FONT-FAMILY: VERDANA' BORDERCOLOR='#FFFFFF' CELLSPACING='1' CELLPADDING='3' WIDTH='100%' BORDER=0>" );
        alertMailMsg.append ( "<TR BGCOLOR='#CCCCFF'><TD COLSPAN='8' ALIGN='CENTER'><B>FORECAST DATA PORTING STATUS REPORT</B></TD></TR>" );
        alertMailMsg.append ( "<TR BGCOLOR='#D3D3D6' ALIGN='CENTER'>" );
        alertMailMsg.append ( "<TD><B>Customer</B></TD><TD><B>Project</B></TD><TD><B>Part Number</B></TD><TD><B>Forecast Year/Month</B></TD><TD><B>Forecast Qty</B></TD><TD><B>Forecast Price</B></TD><TD><B>Porting Status</B></TD><TD><B>Remarks</B></TD>" );
        int size = (forecastDataList!=null) ? forecastDataList.size() : 0;
        for ( int i = 0; i < size; i++ )
        {
            forecastData = (ForecastSheetData) forecastDataList.get(i);
            int statusCode = forecastData.getPortingStatusCode();
            if(statusCode != 0) {

                alertMailMsg.append ( "<TR BGCOLOR='#E6E6E6' ALIGN='CENTER'>" );
                //alertMailMsg.append ( "<TD>").append ( forecastData.getSalesPerson() ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( forecastData.getCustomerName() ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( forecastData.getProjName() ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( forecastData.getPartNumber() ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( forecastData.getYear() + "/" + (forecastData.getMonth()+1) ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( forecastData.getQty() ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( forecastData.getPrice() ).append ( "</TD>" );
                alertMailMsg.append ( "<TD>").append ( "FALSE" ).append ( "</TD>" );
                if(statusCode == PART_NUMBER_NOT_EXIST) {
                    alertMailMsg.append ( "<TD>").append ( "<li><B>Part Number</B> not found" ).append ( "</TD>" );
                } else if(statusCode == PROJECT_NOT_EXIST) {
                    alertMailMsg.append ( "<TD>").append ( "<li><B>Project</B> not found" ).append ( "</TD>" );
                } else if(statusCode == PROJECT_PRODUCT_NOT_EXIST) {
                    alertMailMsg.append ( "<TD>").append ( "<li><B>Project-Product</B> not found" ).append ( "</TD>" );
                }

                alertMailMsg.append ( "</TR>");
            }
        }
        alertMailMsg.append ( "<TR BGCOLOR='#D3D3D6'><TD COLSPAN='8' ALIGN='LEFT'><B>Info:</B></TD></TR>" );
        alertMailMsg.append ( infoMsg );
        alertMailMsg.append ( "</TABLE></BODY>").append( "\n</HTML>" );
        return alertMailMsg.toString();
    }
    private ArrayList<ForecastSheetData> parseSalesForecastFile(File salesForecastFile) throws Exception {

        ArrayList<ForecastSheetData> forecastSheetDataList = new ArrayList<ForecastSheetData>();
        Parser parser = ParserFactory.getXLSParser(salesForecastFile);

        ((XLSParserImpl)parser).setHeaderRowIdx(1);
        ((XLSParserImpl)parser).setDataStartRowIdx(2);
        ParserResult result = parser.parse();
        List headers = result.getHeadings();
        int slotDataStartsFromIdx = 8;
        int qtyRowIdx = 0;
        while (result.next()){
            int columnCount = result.getColumnCount();
            String customerName = result.getString("CUSTOMER");
            String endCustomerName = result.getString("END CUSTOMER");
            String endUserName = result.getString("END USER");
            String projectName = result.getString("PROJECT NAME");
            String partNumber = result.getString("PART NUMBER");
            int priceRowIdx = qtyRowIdx+1;
            int revRowIdx = priceRowIdx + 1;

            for(int i=slotDataStartsFromIdx; i<columnCount; i++) {
                String headerValue = (String)headers.get(i);
                result.moveTo(qtyRowIdx);
                int qty = result.getInt(i);
                result.moveTo(priceRowIdx);
                double price = result.getDouble(i);

                int monthIdx = getMonthIdx(headerValue);
                int year = getYear(headerValue);

                ForecastSheetData forecastSheetData = new ForecastSheetData();
                forecastSheetData.setCustomerName(customerName);
                forecastSheetData.setProjName(projectName);
                forecastSheetData.setPartNumber(partNumber);
                forecastSheetData.setMonth(monthIdx);
                forecastSheetData.setYear(year);
                forecastSheetData.setQty(qty);
                forecastSheetData.setPrice(price);

                forecastSheetDataList.add(forecastSheetData);
                result.moveTo(revRowIdx);
            }
            qtyRowIdx = revRowIdx+1;
            result.moveTo(revRowIdx);
        }

        return forecastSheetDataList;
    }

    private int getMonthIdx(String headerValue) {
        String[] tmp = headerValue.split("-");
        String[] months = DateUtil.getMonthsArray();
        List<String> list = Arrays.asList(months);
        return list.indexOf(tmp[0]);
    }

    private int getYear(String headerValue) {
        String[] tmp = headerValue.split("-");

        return (tmp.length > 1) ? Integer.parseInt(tmp[1]) : -1;
    }
}
